using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Web;
using System.Text;
using System.Web.Script.Serialization;
using WidgetInterface;
using DALServiceData;
//using System.Security;

//[assembly: AllowPartiallyTrustedCallers]
/// <summary>
/// Author: AustralTek 
/// Create date: 2010/05/04
/// http://www.australtek.com
/// Copyright: 2009 - 2010
/// </summary>

/* This interface is used by javascript widgets */
[ServiceContract]
public interface IDALService
{
    /// Return Object because it returns different classes for different widgets
    [OperationContract, WebInvoke(UriTemplate = "CallProcedure")]
    Stream CallProcedure(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "GetFile")]
    Stream GetFile(Stream Parameters);

    // Export objects
    [OperationContract, WebInvoke(UriTemplate = "ExportTo")]
    Stream ExportTo(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "AttachConnection")]
    Stream AttachConnection(Stream Parameters);
}
public enum ExporterType
{
    DestinationPDF = 1
}

[System.ServiceModel.Activation.AspNetCompatibilityRequirements(RequirementsMode = System.ServiceModel.Activation.AspNetCompatibilityRequirementsMode.Allowed)]
public partial class DALService : IDALService
{    
    private String strSource = "DALService";
    #region "Implements"
    /// <summary>
    ///  Execute an arbitrary stored procedure, with the provided parameters and returns
    ///  a json response, customized based on widget class, if no class is provided the resulting 
    ///  dataset is serialized as a list of tables.
    /// </summary>
    /// <param name="Parameters">receive a json serialized ProcedureParameters object</param>
    /// <returns></returns>
    public Stream CallProcedure(Stream Parameters)
    {       
        ProcedureParameters cp;
        JavaScriptSerializer jss = new JavaScriptSerializer();
        String strIPAddress = IPAddress.GetClientIPAddress();
        Logger.Info(strSource, "Initializing");
        try
        {
            /* Get input parameters from input stream */
            StreamReader sr = new StreamReader(Parameters);
            cp = jss.Deserialize<ProcedureParameters>(sr.ReadToEnd());
        }
        catch (FormatException ex)
        {
            Logger.Warn(strSource, "FormatException", ex);
            Logger.Error(strSource, "FormatException", ex);            
            return null;
        }
        try
        {
            if (cp == null)
            {
                Logger.Warn(strSource, "Nulls Parameters");
                throw new UserException("CallProcedure is null");
            }
            if (cp.DatabaseParameters == null)
            {                
                Logger.Warn(strSource, "No Procedure specified");
                throw new UserException("CallProcedure No Procedure specified.");
            }
            else if (cp.DatabaseParameters.Procedure == null)
            {
                Logger.Warn(strSource, "No Procedure specified");                
                throw new UserException("CallProcedure No Procedure specified.");
            }
            
            Logger.Debug(strSource, "Executing Procedure. (Class: " + cp.Class + ", Procedure: " + cp.DatabaseParameters.Procedure + ")");
            /* Execute Procedure with named and typed parameters */
            DataSet ds = ExecuteProcedure(cp);
            if (ds == null)
            {
                Logger.Warn(strSource, "The data set is null");
                throw new UserException("The data set is null");
            }
            Object resultObject = null;
            if (cp.Class != null)
            {
                /* Use Reflection to instatiate a "WidgetType" object and cast it as IWidget */
                Assembly assem = null;
                try
                {
                    String physicalPath = "";
                    if ((cp.Class == null) && (cp.Class == "")) cp.Class = "";
                    physicalPath =  ((cp.Path!=null)&&(cp.Path!=""))?cp.Path:System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath+"Widgets\\"+cp.Class+"\\DLL\\";
                    physicalPath += ((cp.FileName!=null)&&(cp.FileName!=""))?cp.FileName:cp.Class;
                    physicalPath += ((cp.Extension!=null)&&(cp.Extension!=""))?cp.Extension:".dll";                   
                    //if ((cp.RelativePath!=null)&&(cp.RelativePath!=""))  physicalPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "Widgets\\" + cp.RelativePath + "\\DLL\\" + cp.Class + ".dll";                    
                    //else if ((cp.FullPath!=null)&&(cp.FullPath!="")) physicalPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + cp.FullPath + "" + cp.Class + ".dll";
                    //else if ((cp.FullName!=null) && (cp.FullName != "")) physicalPath = cp.FullName;
                    //else physicalPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath +"Widgets\\" + cp.Class + "\\DLL\\" + cp.Class + ".dll";                  
                    assem = Assembly.LoadFrom(physicalPath);
                }
                catch
                {
                    Logger.Warn(strSource, "(Class: " + cp.Class + ", no DLL found, trying to instantiate locally)");                    
                }
                if (assem == null)
                {
                    assem = this.GetType().Assembly;
                }                
                IWidget iWidgetObject = (IWidget)assem.CreateInstance(cp.Namespace + "." + cp.Class);
                if (iWidgetObject == null)
                {                    
                    Logger.Warn(strSource, "Can not instantiate class");
                    throw new Exception("CallProcedure " + cp.Class + " no class found!");
                }
                /* Send the DataSet to widget-custom class to convert result */
                try
                {                    
                    Logger.Debug(strSource, "Instantiate Class, Generating Response");
                    resultObject = iWidgetObject.GenerateResponse(ds);
                }
                catch (UserException ue)
                {                    
                    Logger.Warn(strSource, "fail. (Problems building the " + cp.Class+")",ue);
                    Logger.Error(strSource, "fail", ue);                    
                    throw new UserException("Problems building the " + cp.Class + ". Error: " + ue.Message);
                }
            }
            else
            {
                resultObject = ds.ToDictionary();
            }
            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";

            /* Send the serialized result to the calling widget  */
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(resultObject)));
        }
        catch (UserException ue)
        {
            Logger.Warn(strSource, "", ue);
            Logger.Error(strSource, "", ue);
            return null;
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            return null;
        }
    }

    /// Returns an arbitrary file from the web server, converting its content-typesList to application/json
    public Stream GetFile(Stream Parameters)
    {
        try
        {
            Logger.Info(strSource, "Initializing");
            JSONFileParameters jfp;            
            StreamReader sr = new StreamReader(Parameters);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            jfp = jss.Deserialize<JSONFileParameters>(sr.ReadToEnd());
            if (jfp == null) {
                Logger.Warn(strSource,"Nulls Parameters");
                return null; 
            }
            Logger.Debug(strSource, "Get File: [" + jfp.JSONFile + "]");            

            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            String physicalPath = "";
            if ((jfp.Class == null) && (jfp.Class == "")) jfp.Class = "";
            physicalPath = ((jfp.Path != null) && (jfp.Path != "")) ? jfp.Path : System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "Widgets\\"+jfp.Class+"\\Test\\";
            physicalPath += ((jfp.FileName != null) && (jfp.FileName != "")) ? jfp.FileName : jfp.Class;
            physicalPath += ((jfp.Extension != null) && (jfp.Extension != "")) ? jfp.Extension : ".json"; 
            physicalPath = ((jfp.JSONFile!=null)&&(jfp.JSONFile!=""))?System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + jfp.JSONFile:physicalPath;
            Logger.Debug(strSource, "Open Path File: [" + physicalPath + "]");
            return new FileStream(physicalPath, FileMode.Open, FileAccess.Read);
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            return null;
        }
    }
    /// <summary>
    /// Export objects to pdf.
    /// </summary>
    /// <returns></returns>
    public Stream ExportTo(Stream Parameters)
    {
        try
        {            
            Logger.Info(strSource,"Initializing");
            JavaScriptSerializer jss = new JavaScriptSerializer();
            StreamReader sr = new StreamReader(Parameters);
            Assembly assem = null;
            IExporter iExporterObject;
            Dictionary<string, object> formData = new Dictionary<string, object>();
            string path;
            string fileName;
            string fileExtension;
            
            string[] splitFieldPattern = new string[1] { "&" };
            string[] splitKeyValuePattern = new string[1] { "=" };
            String[] fields = sr.ReadToEnd().Split(splitFieldPattern, StringSplitOptions.None);            
            foreach (string field in fields)
            {
                String[] keyValuePair = field.Split(splitKeyValuePattern, StringSplitOptions.None);
                formData.Add(keyValuePair[0], System.Web.HttpUtility.UrlDecode(keyValuePair[1]));
            }                      

            string exporter = (string)formData["exporter"];
            /* Use Reflection to instatiate a Exporter Object object and cast it as IExporter */
            try
            {                
                assem = Assembly.LoadFrom(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "Addons\\" + exporter + "\\DLL\\" + exporter + ".dll");
            }
            catch (Exception ex)
            {                
                Logger.Warn(strSource,"ExportTo " + exporter + " no DLL found, trying to instantiate locally");
                Logger.Error(strSource,"",ex);
            }
            if (assem == null)
                assem = this.GetType().Assembly;
            iExporterObject = (IExporter)assem.CreateInstance(exporter + "." + exporter);
            if (iExporterObject == null)
            {
                Logger.Warn(strSource, "ExportTo. [Can not instantiate class]");
                throw new UserException("Message: ExportTo. [Can not instantiate class.]");
            }
            Logger.Debug(strSource, "ExportTo. [Instantiate Class, DestinationPDF DLL Loaded.]");
            switch ((ExporterType)Convert.ToInt32(formData["exporterType"]))
            {
                case ExporterType.DestinationPDF:
                    try
                    {
                        ExportToPDF.PDFParameters dPdfParams = jss.Deserialize<ExportToPDF.PDFParameters>(formData[exporter + "Value"].ToString());
                        iExporterObject.DrawFile(dPdfParams);
                    }
                    catch (Exception ex)
                    {
                        Logger.Warn(strSource, "ExportTo [Problems building the DestinationPDF document].",ex);
                        Logger.Error(strSource, "ExportTo [Problems building the DestinationPDF document].", ex);
                        throw new Exception("Problems building the DestinationPDF document. Error: " + ex.Message);
                    }
                    try
                    {
                        fileExtension = ".pdf";
                        path = Path.GetTempFileName().Replace("tmp", fileExtension);
                        iExporterObject.Save(path);
                        fileName = iExporterObject.GetFileName();
                    }
                    catch (Exception ex)
                    {
                        Logger.Warn(strSource, "ExportTo [Problems saving the DestinationPDF document].");
                        Logger.Error(strSource, "", ex);
                        throw new Exception("Problems saving the DestinationPDF document.  Error: " + ex.Message);
                    }
                    break;
                //case ExporterType.DestinationPDF:
                //    ExportToPDF.PDFParameters dPdfParams = jss.Deserialize<ExportToPDF.PDFParameters>(formData[exporter + "Value"].ToString());
                //    iExporterObject.DrawFile(dPdfParams);
                //    fileExtension = ".pdf";
                //    break;
                default:
                    Logger.Warn(strSource, "ExportTo [Exporter type is missing].");                    
                    throw new UserException("Exporter type is missing.");                                        
            }
            

            WebOperationContext.Current.OutgoingResponse.Headers.Add("Content-disposition", string.Format("attachment;filename={0}", fileName + fileExtension));
            //WebOperationContext.Current.OutgoingResponse.ContentType = "application/pdf";
            MemoryStream ms = new MemoryStream();
            return (Stream)new FileStream(path, FileMode.Open);
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            return null;
        }
    }
    /// <summary>
    /// Attach Foreign Connection.
    /// Allow to user to add Connection, outside of the web.config file.
    /// </summary>
    /// <returns></returns>
    public Stream AttachConnection(Stream Parameters)
    {
        try
        {            
            Logger.Info(strSource,"Initializing");
            ForeignConnection fc;
            StreamReader sr = new StreamReader(Parameters);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            fc = jss.Deserialize<ForeignConnection>(sr.ReadToEnd());
            if (fc == null)
            {
                Logger.Warn(strSource,"Nulls Parameters");
                return null;
            }
            Boolean state = false;

            if (fc == null) state = false;
            else
            {
                switch (fc.Action)
                {
                    case ForeignConnection.enumAction.Add:
                        state = ForeignConnections.AttachConnectionStringName(fc.Name, fc.ConnectionString);
                        break;
                    case ForeignConnection.enumAction.Remove:
                        state = ForeignConnections.DetachConnectionStringName(fc.Name);
                        break;
                    default:
                        state = false;
                        break;
                }
            }
            Logger.Debug(strSource, "Attach Connection [" + state + "].");
            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(state)));
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            return null;
        }
    }
    #endregion
    #region "Methods"   
    public DataSet ExecuteProcedure(ProcedureParameters cp)
    {     
        try
        {
            Logger.Info(strSource,"Initializing");
            switch (cp.DatabaseParameters.DBEngine.ToUpper())
            {
                case "SQLSERVER":
                    return GetGenericSQLDataAdapter(cp);
                case "ORACLE":
                    Type thisType = this.GetType();
                    MethodInfo handler = thisType.GetMethod("GetGenericSQLDataAdapterForOracle");
                    if (handler != null)
                    {
                        object[] handlerParams = new object[1];
                        handlerParams[0] = cp;
                        return (DataSet)handler.Invoke(this, handlerParams);
                    }
                    else
                    {
                        Logger.Warn(strSource, "Problems executing procedure: " + cp.DatabaseParameters.Procedure + ", Industrial Dashboard is not configure it to support Oracle connections.");
                        throw new UserException("Problems executing procedure" + cp.DatabaseParameters.Procedure + ". Error: Industrial Dashboard is not configure it to support Oracle connections.");
                    }
                default:
                    {
                        Logger.Warn(strSource, "Problems executing procedure: " + cp.DatabaseParameters.Procedure + ", Invalid 'Connection String Name'.");
                        throw new UserException("Problems executing procedure" + cp.DatabaseParameters.Procedure + ". Error: Invalid 'Connection String Name'.");
                    }
            }
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            throw ex;
        }
    }
    private DataSet GetGenericSQLDataAdapter(ProcedureParameters cp)
    {
        try
        {
            Logger.Info(strSource, "Initializing");
            SqlConnection sqlCon = new SqlConnection();            
            System.Configuration.ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[cp.DatabaseParameters.ConnectionStringName];
            if (css != null)
            {
                sqlCon.ConnectionString = css.ConnectionString;
                Logger.Debug(strSource, "Using web.config.");
            }
            else if (ForeignConnections.Contains(cp.DatabaseParameters.ConnectionStringName))
            {
                sqlCon.ConnectionString = ForeignConnections.GetConnectionString(cp.DatabaseParameters.ConnectionStringName);
                Logger.Debug(strSource, "Using Dictionary Connection.");
            }
            else {
                Logger.Warn(strSource, "Connection can not be null.");
                return null; 
            }

            sqlCon.Open();
            SqlCommand comm = new SqlCommand(cp.DatabaseParameters.Procedure, sqlCon);
            comm.CommandType = CommandType.StoredProcedure;

            if (cp.DatabaseParameters.QueryParameters != null)
            {
                //cp.DatabaseParameters.QueryParameters
                foreach (DALServiceData.QueryParameter param in cp.DatabaseParameters.QueryParameters)
                {
                    SqlParameter sqlParam = new SqlParameter(param.Name, (SqlDbType)param.Type);
                    if ((SqlDbType)param.Type == SqlDbType.DateTime)
                    {
                        sqlParam.Value = DateTime.Parse((string)param.Value, CultureInfo.GetCultureInfo("en-US"));
                    }
                    else
                    {
                        sqlParam.Value = param.Value;
                    }
                    comm.Parameters.Add(sqlParam);
                }
            }

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            da.Dispose();
            sqlCon.Close();
            return ds;
        }
        catch (SqlException sqle)
        {
            Logger.Warn(strSource, "", sqle);
            Logger.Error(strSource, "", sqle);
            throw sqle;
        }
        catch (Exception ex)
        {
            Logger.Warn(strSource, "", ex);
            Logger.Error(strSource, "", ex);
            throw ex;
        }
    }
    #endregion
}


